package dao;

import db.DbUtil;
import entity.Goods;
import entity.Type;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 蒋银辉
 * @version 1.0
 */
public class TypeDao {

    public List<Type> selectAllTypes() {
        List<Type> list= DbUtil.getInstance().queryList("select * from type",null, Type.class);
        return list;
    }
    public List<Type> queryTypePage(int start, int size, String condition, List<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select * from type where "+ condition +" limit ?,?";
            System.out.println(sql);
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ps.setObject(parmas.size()+1,start);
            ps.setObject(parmas.size()+2,size);

            ResultSet rs = ps.executeQuery();
            List<Type> list = new ArrayList<>();
            while(rs.next()) {
                Type type = new Type();
                type.setTypeId(rs.getInt("type_id"));
                type.setTypeName(rs.getString("type_name"));
                type.setTypeDescription(rs.getString("type_description"));
                list.add(type);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return null;
    }

    public Integer total_types(String condition, List<Object> parmas) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "select count(*) from type where "+ condition;
            ps = conn.prepareStatement(sql);
            for (int i =1;i <= parmas.size();i++){
                ps.setObject(i,parmas.get(i-1));
            }
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(ps, conn);
        }
        return 0;
    }

    public int updateType(String TypeName,String TypeDescription,String TypeID) {
        int ret = DbUtil.getInstance().execute("update type set type_name=?,type_description=? where type_id=?", new Object[]{TypeName,TypeDescription,TypeID});
        return ret;
    }

    public int deleteType(Integer id) {
        int ret = DbUtil.getInstance().execute("delete from type where type_id=?", new Object[]{id});
        return ret;
    }

    public int addType(String typeName, String typeDesc) {
        int ret = DbUtil.getInstance().execute("insert into type(type_name,type_description) values(?,?)", new Object[]{typeName,typeDesc});
        return ret;
    }

    private Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/shopping?useUnicode=true&characterEncoding=utf8",
                    "root", "123456");
            return conn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    private void closeConnection(Statement stmt, Connection conn) {
        try {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int sureType(String typeName) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            String sql = "select count(*) from type where type_name=" + typeName;
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(stmt, conn);
        }
        return 0;
    }


}